#ifndef TESTSQLITE_H
#define TESTSQLITE_H

#include <QObject>
#include <QDebug>
#include <QCoreApplication>

#include "qsqlite.h"

class TestSqlite : public QObject
{
    Q_OBJECT
public:
    explicit TestSqlite(QObject *parent = nullptr):QObject(parent){
        // this->clear();
        // this->test();
        // this->list();
        // this->table1();
        // this->table2();
        // this->table3();
        // this->table4();
        // this->addOne1();
        // this->addOne2();
        // this->addArray1();
        // this->addArray2();
        // this->remove();
        // this->removeWhere();
        // this->update1();
        // this->update2();
        // this->update3();
        // this->count();
        // this->limit1();
        // this->limit2();
        // this->limit3();
        // this->limit4();
    }

    QString db(){
        auto root = QCoreApplication::applicationDirPath();
        root = root.replace("\\","/");
        QString db = QString("test.db");

        if(root.endsWith("/")){
            return QString("%1../../%2").arg(root,db);
        }
        return QString("%1/../../%2").arg(root,db);
    }

    void test(){
        QSqlite session(this->db());
        qDebug() << "valid" << session.isValid();
        if(!session.open()){
            qDebug() << "open error";
            return;
        }
        qDebug() << "open" << session.isOpen();
    }

    void print(QDataSet data){
        qDebug() << "";
        qDebug() <<"sql=" << data.sql << "table count="<< data.count() << "error=" << data.error;
        for(int i=0;i<data.count();i++){
            auto table = data.at(i);
            qDebug() << "table index" << QString::number(i);
            for(int j=0;j<table.count();j++){
                auto row = table.at(j);
                qDebug() << row;
            }
        }
    }

    void print(QDataTable data){
        qDebug() << "";
        for(int i=0;i<data.count();i++){
            auto row = data.at(i);
            qDebug() << row;
        }
    }

    void clear(){
        QSqlite session(this->db());
        session.excute("delete from student;");
    }

    void list(){
        QSqlite session(this->db());
        // 全部列
        auto sql = QString("select * from student");
        auto query = session.list(sql);
        this->print(query);

        // 部分列
        sql = QString("select id,name from student;");
        query = session.list(sql);
        this->print(query);

        // where 条件
        sql = QString("select * from address where id = 1;");
        query = session.list(sql);
        this->print(query);

        // where 条件,QT 绑定参数方式1
        sql = QString("select * from address where id = ?;");
        query = session.list(sql,QVariantList{1});
        this->print(query);

        // where 条件,QT 绑定参数方式2
        sql = QString("select * from address where id = :id;");
        query = session.list(sql,QVariantList{1});
        this->print(query);
    }

    void table1(){
        QSqlite session(this->db());
        auto table = session.table("student");
        this->print(table);

        table = session.table("student","id=1");
        this->print(table);

        table = session.table("student",QString(),"id desc");
        this->print(table);

        table = session.table("student",QString(),QStringList{"id desc"});
        this->print(table);
    }

    void table2(){
        QSqlite session(this->db());
        auto table = session.table("student",QVariantMap{{"id",1}});
        this->print(table);

        table = session.table("student",QVariantMap{{"id",2},{"name","n2"}});
        this->print(table);

        table = session.table("student",QVariantMap{},"id desc");
        this->print(table);
    }

    void table3(){
        QSqlite session(this->db());
        QStringList columns = QStringList{"id","name","address"};
        auto table = session.table("student",columns);
        this->print(table);

        columns = QStringList{"id","name"};
        table = session.table("student",columns);
        this->print(table);

        columns = QStringList{"id","name","address"};
        table = session.table("student",columns,"id = 1");
        this->print(table);

        columns = QStringList{"id","name"};
        table = session.table("student",columns,"","id desc");
        this->print(table);

        columns = QStringList{"id","name"};
        table = session.table("student",columns,"",QStringList{"id desc"});
        this->print(table);
    }

    void table4(){
        QSqlite session(this->db());
        QStringList columns = QStringList{"id","name","address"};
        auto table = session.table("student",columns,QVariantMap{{"id",1}});
        this->print(table);

        table = session.table("student",columns,QVariantMap{{"id",1}},"id desc");
        this->print(table);

        table = session.table("student",columns,QVariantMap(),"id desc");
        this->print(table);

        table = session.table("student",columns,QVariantMap{{"id",1}},QStringList{"id desc"});
        this->print(table);
    }

    void addOne1(){
        QSqlite session(this->db());
        // 全部列
        auto data = QVariantMap{{"id",1},{"name","n1"},{"address","addr1"}};
        auto added = session.add("student",data);
        qDebug() << "added" << added;
        auto query = session.list("select * from student where id=:id;",QVariantList{1});
        this->print(query);

        // 部分列
        data = QVariantMap{{"id",2},{"name","n2"}};
        added = session.add("student",data);
        qDebug() << "added" << added;
        query = session.list("select * from student where id=:id;",QVariantList{2});
        this->print(query);
    }

    void addOne2(){
        QSqlite session(this->db());
        QStringList columns = QStringList{"id","name","address"};
        auto data = QVariantMap{{"id",1},{"name","n1"},{"address","addr1"}};
        // 全部列
        auto added = session.add("student",columns,data);
        qDebug() << "added" << added;
        auto query = session.list("select * from student where id=:id;",QVariantList{1});
        this->print(query);

        // 部分列
        columns = QStringList{"id","address"};
        data = QVariantMap{{"id",2},{"name","n2"},{"address","addr2"}};
        added = session.add("student",columns,data);
        qDebug() << "added" << added;
        query = session.list("select * from student where id=:id;",QVariantList{2});
        this->print(query);
    }

    void addArray1(){
        QSqlite session(this->db());
        // 全部列
        QList<QVariantMap> data = QList<QVariantMap>{
                QVariantMap{{"id",1},{"name","n1"},{"address","addr1"}},
                QVariantMap{{"id",2},{"name","n2"},{"address","addr2"}},
                QVariantMap{{"id",3},{"name","n3"},{"address","addr3"}}
    };

        auto added = session.add("student",data);
        qDebug() << "added" << added;
        auto query = session.list("select * from student;");
        this->print(query);

        // 部分列
        data = QList<QVariantMap>{
                QVariantMap{{"id",4},{"address","add4"}},
                QVariantMap{{"id",5},{"name","n5"}},
                QVariantMap{{"id",6},{"name","n6"},{"address","addr6"}}
    };

        added = session.add("student",data);
        qDebug() << "added" << added;
        query = session.list("select * from student;");
        this->print(query);
    }

    void addArray2(){
        QSqlite session(this->db());
        QStringList columns = QStringList{"id","name","address"};
        // 全部列
        QList<QVariantMap> data = QList<QVariantMap>{
                QVariantMap{{"id",1},{"name","n1"},{"address","addr1"}},
                QVariantMap{{"id",2},{"name","n2"},{"address","addr2"}},
                QVariantMap{{"id",3},{"name","n3"},{"address","addr3"}}
    };

        auto added = session.add("student",columns,data);
        qDebug() << "added" << added;
        auto query = session.list("select * from student;");
        this->print(query);

        // 部分列
        columns = QStringList{"id","address"};
        data = QList<QVariantMap>{
                QVariantMap{{"id",4},{"name","n4"},{"address","addr4"}},
                QVariantMap{{"id",5},{"name","n5"},{"address","addr5"}},
                QVariantMap{{"id",6},{"name","n6"},{"address","addr6"}}
    };
        added = session.add("student",columns,data);
        qDebug() << "added" << added;
        query = session.list("select * from student;");
        this->print(query);
    }

    void remove(){
        QSqlite session(this->db());
        auto removed = session.remove("student");
        qDebug() << "remove" << removed;
    }

    void removeWhere(){
        QSqlite session(this->db());
        // 正确条件,返回 true
        auto removed = session.remove("student",QVariantMap{{"id",1},{"name","n1"}});
        qDebug() << "remove" << removed;

        // 错误条件,返回 true
        removed = session.remove("student",QVariantMap{{"id",1},{"name","n2"}});
        qDebug() << "remove" << removed;
    }

    void update1(){
        QSqlite session(this->db());
        // 一个字符串条件
        auto data = QVariantMap{{"name","name1"},{"address","address1"}};
        auto updated = session.update("student",data,"id=1");
        qDebug() << "update" << updated;
        auto query = session.list("select * from student where id = 1;");
        this->print(query);

        // 一个 QVariantMap 条件
        data = QVariantMap{{"name","name2"},{"address","address2"}};
        updated = session.update("student",data,QVariantMap{{"id",2}});
        qDebug() << "update" << updated;
        query = session.list("select * from student where id = 2;");
        this->print(query);

        // 多个字符串条件
        data = QVariantMap{{"address","address3"}};
        updated = session.update("student",data,"id=3 and name='n3'");
        qDebug() << "update" << updated;
        query = session.list("select * from student where id = 3;");
        this->print(query);

        // 多个 QVariantMap 条件
        data = QVariantMap{{"address","address5"}};
        updated = session.update("student",data,QVariantMap{{"id",5},{"name","n5"}});
        qDebug() << "update" << updated;
        query = session.list("select * from student where id = 5;");
        this->print(query);
    }

    void update2(){
        QSqlite session(this->db());
        // 指定有效列,一个字符串条件
        QStringList columns = QStringList{"name","address"};
        auto data = QVariantMap{{"name","name1"},{"address","address1"},{"age",20}};
        auto updated = session.update("student",columns,data,"id=1");
        qDebug() << "update" << updated;
        auto query = session.list("select * from student where id = 1;");
        this->print(query);

        // 指定无效列,一个字符串条件
        columns = QStringList{"name","age"};
        data = QVariantMap{{"name","name2"},{"address","address2"},{"age",20}};
        updated = session.update("student",columns,data,"id=2");
        qDebug() << "update" << updated;
        query = session.list("select * from student where id = 2;");
        this->print(query);
    }

    void update3(){
        QSqlite session(this->db());
        // 指定有效列,一个 QVariantMap 条件
        QStringList columns = QStringList{"name","address"};
        auto data = QVariantMap{{"name","name1"},{"address","address1"},{"age",20}};
        auto updated = session.update("student",columns,data,QVariantMap{{"id",1}});
        qDebug() << "update" << updated;
        auto query = session.list("select * from student where id = 1;");
        this->print(query);

        // 指定有效列,多个 QVariantMap 条件
        columns = QStringList{"address"};
        data = QVariantMap{{"name","name2"},{"address","address2"},{"age",20}};
        updated = session.update("student",columns,data,QVariantMap{{"id",2},{"name","n2"}});
        qDebug() << "update" << updated;
        query = session.list("select * from student where id = 2;");
        this->print(query);

        // 指定无效列,一个 QVariantMap 条件
        columns = QStringList{"name","age"};
        data = QVariantMap{{"name","name3"},{"address","address3"},{"age",20}};
        updated = session.update("student",columns,data,QVariantMap{{"id",3}});
        qDebug() << "update" << updated;
        query = session.list("select * from student where id = 3;");
        this->print(query);

        // 指定无效列,多个 QVariantMap 条件
        columns = QStringList{"address","age"};
        data = QVariantMap{{"name","name5"},{"address","address5"},{"age",20}};
        updated = session.update("student",columns,data,QVariantMap{{"id",5},{"name","n5"}});
        qDebug() << "update" << updated;
        query = session.list("select * from student where id = 5;");
        this->print(query);
    }

    void count(){
        QSqlite session(this->db());
        auto count = session.count("student","id=1");
        qDebug() << "count" << count;

        count = session.count("student",QVariantMap{{"id",1},{"name","n1"}});
        qDebug() << "count" << count;
    }

    void limit1(){
        QSqlite session(this->db());
        auto table = session.limit("student",QString(),"id desc","2");
        this->print(table);

        table = session.limit("student",QString(),"id desc","2,2");
        this->print(table);

        table = session.limit("student",QString("id=1"),"id desc","0,2");
        this->print(table);

        table = session.limit("student",QString("id=1"),QStringList{"id desc"},"0,2");
        this->print(table);
    }

    void limit2(){
        QSqlite session(this->db());
        auto table = session.limit("student",QVariantMap(),"id desc","1,2");
        this->print(table);

        table = session.limit("student",QVariantMap{{"id",1}},"id desc","2");
        this->print(table);

        table = session.limit("student",QVariantMap{{"id",1}},QStringList{"id desc"},"2");
        this->print(table);
    }

    void limit3(){
        QSqlite session(this->db());
        QStringList columns = QStringList{"id","address"};
        auto table = session.limit("student",columns,QString(),"id desc","2");
        this->print(table);

        table = session.limit("student",columns,QString(),"id desc","2,2");
        this->print(table);

        table = session.limit("student",columns,QString("id=1"),"id desc","0,2");
        this->print(table);

        table = session.limit("student",columns,QString("id=1"),QStringList{"id desc"},"0,2");
        this->print(table);
    }

    void limit4(){
        QSqlite session(this->db());
        QStringList columns = QStringList{"id","address"};
        auto table = session.limit("student",columns,QVariantMap(),"id desc","1,2");
        this->print(table);

        columns = QStringList{"id","name"};
        table = session.limit("student",columns,QVariantMap{{"id",1}},"id desc","2");
        this->print(table);

        table = session.limit("student",columns,QVariantMap{{"id",1}},QStringList{"id desc"},"2");
        this->print(table);
    }
};

#endif // TESTSQLITE_H
